package com.tbsoft.ehr.dao.notify;

import java.sql.SQLException;
import java.util.List;

import org.hibernate.HibernateException;
import org.hibernate.SQLQuery;
import org.hibernate.Session;
import org.hibernate.transform.Transformers;
import org.springframework.orm.hibernate3.HibernateCallback;

import com.tbsoft.ehr.dao.BaseDaoImpl;
import com.tbsoft.ehr.model.NotifyFile;
/**
 * @desc 系统公告Dao实现
 * @author vastdream
 *
 */
public class NotifyDaoImpl  extends BaseDaoImpl<NotifyFile>
implements NotifyDao {

	@SuppressWarnings("unchecked")
	@Override
	public List<NotifyFile> getFileListbyType(final String type,final String userId) {
		
		return getHibernateTemplate().executeFind(new HibernateCallback() {
			
			@Override
			public Object doInHibernate(Session session) throws HibernateException,
					SQLException {
				String sql="select nf.* from EHR_NOTIFYFILE nf ";
						sql+= " where nf.status='A' and  nf.NOTIFY_TYPE='"+type+"'";
						if(userId != null && !userId.isEmpty()){
							sql+=" and ( nf.SC_ID is null or nf.SC_ID = (select si.SC_ID from EHR_STAFFINFO si";
							sql+="  where si.USER_ID='"+userId+"' and si.`STATUS`='A'))";
						}
						sql += " order by nf.update_time desc";
				SQLQuery query = session.createSQLQuery(sql);
				query.addEntity(NotifyFile.class);
				List<?> list = query.list();
				return list;
			}
		});
	}
	@Override
	public NotifyFile getNotifyFileById(String fileId) {
		return (NotifyFile) getHibernateTemplate().get(NotifyFile.class, Integer.valueOf(fileId));
	}
	@SuppressWarnings("unchecked")
	@Override
	public List<NotifyFile> getNotifyFileList(final String type, final String scId) {
		return getHibernateTemplate().executeFind( new HibernateCallback() {
			
			@Override
			public Object doInHibernate(Session session) throws HibernateException, SQLException {
				String sql="select nf.file_id as fileId,nf.file_name as fileName,nf.notify_type as notifyType,sc.SC_NAME as serviceName from EHR_NOTIFYFILE nf ";
				sql += "left join EHR_SERVICECUSTOMER sc ON  sc.sc_id = nf.sc_id AND sc.STATUS =  'A'";
				sql += " where nf.status='A'";
				if(type != null && !type.isEmpty()){
					sql += " and nf.NOTIFY_TYPE='" + type + "'";
				}
				if(scId != null && !scId.isEmpty()){
					sql += " and nf.SC_ID='" + scId +"'";
				}
				sql += " order by nf.update_time desc";
				SQLQuery query = session.createSQLQuery(sql);
				query.addScalar("serviceName", new org.hibernate.type.StringType());
				query.addScalar("fileId", new org.hibernate.type.IntegerType());
				query.addScalar("fileName", new org.hibernate.type.StringType());
				query.addScalar("notifyType", new org.hibernate.type.StringType());
				query.setResultTransformer(Transformers.aliasToBean(NotifyFile.class));
				List<?> list = query.list();
				return list;
			}
		});
	}

}
